Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data.SqlClient

Namespace MyDAO
    Public Class InsertStringBuilder
        Inherits AssignmentStringBuilder

        Public SelectTable As MyDataTableBase

        Public Sub New(ByVal tableName As String)
            MyBase.New(tableName)
        End Sub

        Public Assignments As New List(Of MyDataAssignStatement)

        Public Sub AddColumnAssignment(ByVal columnName As String, ByVal selectColumnName As String)
            Me.Assignments.Add(New MyDataAssignStatement(columnName, New MyColumnExpression(selectColumnName)))
        End Sub

        Public Overrides Sub AddAssignment(ByVal columnName As String, ByVal value As Object)
            Me.AddVarAssignment(columnName)
            Me.AddWithValue(columnName, value)
        End Sub

        Public Sub AddVarAssignment(ByVal columnName As String)
            Me.AddVarAssignment(columnName, columnName)
        End Sub

        Public Sub AddVarAssignment(ByVal columnName As String, ByVal variable As String)
            Me.Assignments.Add(New MyDataAssignStatement(columnName, New MyVariableExpression(variable)))
        End Sub

        Public Sub AddSelectTable(ByVal tableName As String)
            Me.SelectTable = New MyDataTable(tableName)
        End Sub

        Public GroupColumns As New List(Of MyDataExpressionBase)

        Public Sub AddGroupColumn(ByVal ParamArray columnNames() As String)
            For Each columnName As String In columnNames
                Me.GroupColumns.Add(New MyColumnExpression(columnName))
            Next
        End Sub

        Public Overrides Function Build() As MyStringBuilder
            Dim sql As New MyStringBuilder

            sql.AppendFormat("insert {0} ", Me.Table)

            Dim expressions As New List(Of String)

            'columns
            sql.AppendFormat("( ")
            expressions.Clear()

            For Each assignment As MyDataAssignStatement In Me.Assignments
                expressions.Add(assignment.Column.ToString())
            Next

            sql.AppendFormat(String.Join(",", expressions.ToArray()))
            sql.AppendFormat(") ")

            'values
            sql.AppendFormat("select ")
            expressions.Clear()

            For Each assignment As MyDataAssignStatement In Me.Assignments
                expressions.Add(assignment.Expression.ToString())
            Next

            sql.AppendFormat(String.Join(",", expressions.ToArray()))
            sql.AppendFormat(" ")

            'table
            If Me.SelectTable IsNot Nothing Then
                sql.AppendFormat("from {0} ", Me.SelectTable)
            End If

            'join
            expressions.Clear()

            For Each relation As MyDataRelationBase In Me.Relations
                'sql.AppendFormat("
                expressions.Add(relation.ToString())
            Next

            sql.AppendFormat(String.Join(" ", expressions.ToArray()))
            sql.AppendFormat(" ")

            'condition
            If Me.Condition IsNot Nothing Then
                sql.AppendFormat("where ")
                sql.AppendFormat(Me.Condition.ToString())
                sql.AppendFormat(" ")
            End If

            'group column
            If Me.GroupColumns.Count > 0 Then
                sql.AppendFormat("group by ")

                expressions.Clear()

                For Each groupColumn As MyDataExpressionBase In Me.GroupColumns
                    expressions.Add(groupColumn.ToString())
                Next

                sql.AppendFormat(String.Join(",", expressions.ToArray()))
            End If

            Return sql
        End Function

    End Class
End Namespace
